<?php
/**
 * Simple database connection and database access wrapper class.
 *
 * LICENSE:
 *
 * This library is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with This library. If not, see <{@link http://www.gnu.org/licenses/}>.
 *
 * @link http://code.google.com/p/phpstartapp/
 * @author Bojan Mauser <bmauser@gmail.com>
 * @copyright Bojan Mauser 2009
 * @package psa
 * @version $Id: $
 */


/**
 * Simple database connection class and database access wrapper class.
 * Handles database connection and executes queries against the database.
 * 
 * PSA uses {@link http://www.php.net/pdo PHP Data Objects (PDO)} extension for 
 * database access. Methods in this class are wrappers for corresponding PDO methods.
 * You should also use PDO for database access in your applications
 * based on PSA because database connection is already open and you dont't want
 * to open another connection to database form another driver like mysqli.
 * Also you shouldn't open new PDO connection if it's not necessary, instead you 
 * should use already open connection by PSA.
 * 
 * If you don't want to use this class for database connection in your model
 * methods, you should at least use the same connection object that can be accessed
 * through <kbd>$this->psa_database->connection</kbd> in model method.
 * 
 * See in examples below how you can access Psa_Pdo object or PDO connection object
 * in model methods.
 *
 * <b>Examples:</b>
 *
 * <code>
 * class my_model extends Psa_Plugin_Model {
 * 
 * 	// returns all records from table
 * 	function get_all_records(){
 * 		
 * 		$this->psa_database->query("SELECT * FROM table");
 * 		return $this->psa_database->fetch_all();
 * 	}
 * 	
 * 
 * 	// returns selected row from table
 * 	function get_one_row(){
 * 		
 * 		$this->psa_database->query("SELECT * FROM table WHERE id = 123");
 * 		return $this->psa_database->fetch_row();
 * 	}
 * 	
 * 
 * 	// iterates through each row
 * 	function sum_all(){
 * 		
 * 		$sum = 0;
 * 		
 * 		$this->psa_database->query("SELECT * FROM table");
 * 		while($row = $database->fetch_row()){
 * 			$sum += $row['column1'] + $row['column2'] + $row['column3'];
 * 		}
 * 		
 * 		return $sum;
 * 	}
 * 	
 * 
 * 	// executes prepared statement
 * 	function save_values($value1, $value2, $value3){
 * 		
 * 		// query
 * 		$sql = "INSERT INTO table (column1, column2, column3) VALUES (?,?,?)";
 * 		
 * 		// argument for execute() method	
 * 		$qparams = array($value1,$value2,$value3);	
 * 		
 * 		// execute query
 * 		try{
 * 			$this->psa_database->execute($qparams,$this->psa_database->prepare($sql));
 * 		}
 * 		catch (Psa_Db_Exception $e){
 * 			return 0;
 * 		}
 * 	
 * 		return $this->psa_database->last_insert_id('seq_name');
 * 	}
 * 	
 * 
 * 	// use transactions
 * 	function save_values2($value1, $value2){
 * 		
 * 		// start transaction
 * 		// be sure to have database connection before beginTransaction()
 *		$this->psa_database->connect();
 * 		$this->psa_database->connection->beginTransaction();
 * 
 * 		// queries
 * 		$sql1 = "INSERT INTO table1 (column1) VALUES (?)";
 * 		$this->psa_database->execute(array($value1),$this->psa_database->prepare($sql1));
 * 		
 * 		$sql2 = "INSERT INTO table2 (column2) VALUES (?)";
 * 		$this->psa_database->execute(array($value2),$this->psa_database->prepare($sql2));
 * 		
 * 		// commit
 * 		$this->psa_database->connection->commit();
 * 	}
 * }
 * </code>
 *
 * @see config.php
 */


class Psa_Pdo{

	/**
	 * PDO database connection object.
	 * 
	 * @var PDO
	 * @see connect()
	 */
	public $connection = null;


	/**
	 * Query result.
	 * Result from {@link query()} or {@link execute()} method.
	 * 
	 * @var PDOStatement 
	 * @see query()
	 * @see execute()
	 */
	public $result;


	/**
	 * Associative array with all fetched records.
	 * It is set by {@link fetch_all()} method.
	 * 
	 * @var array
	 * @see fetch_all()
	 */
	public $fetched_rows;


	// Database connection parameters. If are set, will be used by connect() function.

	/**
	 * PDO data source name.
	 * @var string
	 */
	public $dsn = null;
	/**
	 * Database username.
	 * @var string
	 */
	public $username = null;
	/**
	 * Database password.
	 * @var string
	 */
	public $password = null;
	/**
	 * PDO driver options
	 * @var array
	 */
	public $driver_options = array();


	/**
	 * If <var>$sql</var> parameter is set query is executed against database with {@link query()} method
	 * 
	 * @param string $sql database query
	 * @see query()
	 */
	public function __construct($sql = null){
		if($sql)
			$this->query($sql);
	}


	/**
	 * Performs a query against the database.
	 * Throws a {@link Psa_Db_Exception} if query fails. 
	 * 
	 * @param string $sql database query
	 * @return boolean true if success
	 * @throws Psa_Db_Exception
	 */
	public function query($sql){

		// connect to database if connection don't exists
		if (!$this->connection){
			$this->connect();
		}
		
		// destroy previous result
		$this->result = null;
		
		// run query against the database
		try{
			$this->result = $this->connection->query($sql);		
		}
		catch(PDOException $e){				
			
			$error = $this->connection->errorInfo();
			
			include_once 'Psa_Db_Exception.class.php';
			throw new Psa_Db_Exception('SQL error: ' . $e->getMessage() . '. In query: ' . $sql, $e->getCode());
		}
		
		// if success
		return true;
	}

	
	/**
	 * Returns the number of rows affected by a DELETE, INSERT, or UPDATE statement.
	 * @return int|boolean number of rows affected, false if there is no result
	 */
	public function affected_rows(){

		// connect to database if connection don't exists
		if ($this->result)
			return $this->result->rowCount();
		else
			return false;
	}

	
	/**
	 * Returns the ID of the last inserted row
	 * @param string $sequence sequence name
	 * @return string
	 */
	public function last_insert_id($sequence = null){

		return $this->connection->lastInsertId($sequence);
	}


	/**
	 * Connects to the database.
	 * If called without arguments it first uses connection parameters from object properties ({@link $dsn}, 
	 * {@link $username}, {@link $password}, {@link $driver_options}) and if
	 * {@link $dsn} value is not set takes connection parameters from <var>$PSA_CFG</var> global array
	 * from {@link config.php}.
	 * Throws a {@link Psa_Fatal_Error_Exception} if the attempt to connect to the requested database fails.
	 *  
	 * @param string $dsn PDO data source name
	 * @param string $username username
	 * @param string $password password
	 * @param string $driver_options pdo driver-specific connection options
	 * @param bool $throw_PDOException if true PDOException will be thrown instead Psa_Fatal_Error_Exception
	 * @return boolean
	 * @throws Psa_Fatal_Error_Exception
	 * @see config.php
	 */
	public function connect($dsn = null, $username = null, $password = null, $driver_options = null, $throw_PDOException = 0){

		// if connected to the database
		if(!$this->connection){
			
			global $PSA_CFG;
			
			// if connection parameters are passed as method arguments
			if($dsn){
				$this->dsn = $dsn;
				$this->username = $username;
				$this->password = $password;
				$this->driver_options = $driver_options;
			}
				
			// if connection parameters are not set as object properties use default
			else if(!$this->dsn){
				$this->dsn = $PSA_CFG['pdo']['dsn'];
				$this->username = $PSA_CFG['pdo']['username'];
				$this->password = $PSA_CFG['pdo']['password'];
				if(isset($PSA_CFG['pdo']['driver_options']))
					$this->driver_options = $PSA_CFG['pdo']['driver_options'];
			}

			// Connect to database server
			try{
				$this->connection = new PDO($this->dsn, $this->username, $this->password, $this->driver_options);
			}
			catch(PDOException $e){				
				
				if($throw_PDOException)
					throw $e;
				
				// check if PDO extension is enabled
				if(substr($this->dsn,0,6) == 'mysql:' && !extension_loaded('pdo_mysql'))
					$msg1 = "pdo_mysql extension not enabled.";
				else if(substr($this->dsn,0,6) == 'pgsql:' && !extension_loaded('pdo_pgsql'))
					$msg1 = "pdo_pgsql extension not enabled.";
				else
					$msg1 = '';
				
				include_once 'Psa_Fatal_Error_Exception.class.php';
				$message = "Unable to connect to database. $msg1 " . $e->getMessage();
				
				// log message cannot be written into the database if there is problem with database connection
				if($PSA_CFG['logging']['storage']['psa_default']['type'] == 'database')
					throw new Psa_Fatal_Error_Exception($message, $message, $e->getCode(), false);
				else
					throw new Psa_Fatal_Error_Exception($message, $message, $e->getCode());
			}
		}
		
		// return true on success or already connected
		return true;
	}


	/**
	 * Returns all rows from result.
	 * And fills {@link $fetched_rows} member associative array.
	 * 
	 * @return array associative array with all records from result of last query.
	 * @see $fetched_rows
	 */
	public function fetch_all(){
		if ($this->result){
				
			$this->fetched_rows = $this->result->fetchAll(PDO::FETCH_ASSOC);
			return $this->fetched_rows;
		}
		return array();
	}


	/**
	 * Fetch next row from result.
	 * 
	 * @return array associative array. A row from result.
	 */
	public function fetch_row() {
		if ($this->result){	
			
			if($row = $this->result->fetch(PDO::FETCH_ASSOC))
				return $row;
			
		}
		return array();
	}
	
	
	/**
	 * Returns only one column from a result.
	 * 
	 * @param string $column_name name of the column
	 * @return array all values from the column.
	 */
	public function fetch_column($column_name) {
		
		$all_rows = $this->fetch_all();
		$return = array();
		
		if($all_rows && array_key_exists($column_name, $all_rows[0])){
			foreach ($all_rows as $value) {
				$return[] = $value[$column_name];
			}
			return $return;
		}

		return array();
	}


	/**
	 * Escapes special characters in a string for use in a SQL statement.
	 * 
	 * @param string $value string to escape
	 * @return string escaped string
	 */
	public function escape($value) {

		// connect to database if connection don't exists
		if (!$this->connection){
			$this->connect();
		}
		
		return $this->connection->quote($value);
	}
	
	
	/**
	 * Prepares a statement for execution.
	 * Returns a statement object and also sets {@link $result} member object.
	 * 
	 * @param string $sql
	 * @return object PDOStatement 
	 * @see execute()
	 */
	public function prepare($sql) {

		// connect to database if connection don't exists
		if (!$this->connection){
			$this->connect();
		}
		
		// prepare query
		$this->result = $this->connection->prepare($sql);
		return $this->result;
	}
	
	
	/**
	 * Executes a prepared statement.
	 * Throws a {@link Psa_Db_Exception} if statement fails.
	 * 
	 * @param array $params an array of values with as many elements as there are bound 
	 * parameters in the SQL statement being executed
	 * @param object PDOStatement $statement if not set {@link $result} is used
	 * @param bool $throw_PDOException if true PDOException will be thrown instead Psa_Db_Exception
	 * @return bool
	 * @see prepare()
	 */
	public function execute($params = null, $statement = null, $throw_PDOException = 0) {
		
		// if statement is not set
		if(!$statement)
			$statement = $this->result;
		else
			$this->result = $statement;
			
		// execute prepared query
		try{
			if(is_array($params))
				$statement->execute($params);
			else
				$statement->execute();
		}
		catch(PDOException $e){				
			
			if($throw_PDOException)
				throw $e;
			
			$error = $this->connection->errorInfo();
			
			if(is_array($params))
				$query_params_str = ' Query input parameters: ' . implode(',',$params);
			else
				$query_params_str = '';
			
			include_once 'Psa_Db_Exception.class.php';
			throw new Psa_Db_Exception('SQL error: ' . $e->getMessage() . '. In query: ' . $statement->queryString . $query_params_str, $e->getCode());
		}
		
		// if success
		return true;
	}
}

